﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Linq;

namespace Architecture.DataLayer.DbAccess
{
    /// <summary>
    /// Es la implementación de la interfaz para acceso directo a datos
    /// ESPECIFICAMENTE para SQL Server
    /// </summary>
    public class SqlDataHelper : IDataHelper<SqlDataReader, SqlParameter[], SqlParameter>, IDisposable
    {
        /// <summary>
        /// Nombre estándar para el "parámetro" de retorno de un stored procedure
        /// </summary>
        private const string OutputParameterName = "@Return_Value";

        /// <summary>
        /// Conexión con la base de datos de SQL Server
        /// </summary>
        protected SqlConnection Connection;

        /// <summary>
        /// DataReader de SQL que contiene resultados de una consulta o de stored procedure de consulta
        /// </summary>
        protected SqlDataReader Reader;

        /// <summary>
        /// Creo el SQLDataHelper con una conexión a una base de datos de SQL Server
        /// </summary>
        /// <param name="connectionString">String de conexión a la base de datos</param>
        public SqlDataHelper(string connectionString)
        {
            Connection = new SqlConnection(connectionString);
            Reader = null;
        }

        #region IDataHelper<SqlDataReader,SqlParameter[],SqlParameter> Members

        /// <summary>
        /// Opens the connection if it is not already opened.
        /// </summary>
        /// <returns></returns>
        public bool OpenConnection()
        {
            if (Connection.State != ConnectionState.Open)
                Connection.Open();
            return true;
        }

        /// <summary>
        /// Closes the connection if it is not already closed.
        /// </summary>
        public void CloseConnection()
        {
            CloseReader();
            if (Connection.State != ConnectionState.Closed)
                Connection.Close();
        }

        /// <summary>
        /// Executes an stored procedure that returns rows, when the CloseReader method is called the connection to the database will be closed.
        /// </summary>
        /// <param name="spName">The name of the stored procedure that will be excecuted</param>
        /// <param name="parameters">The parameters associated to the stored procedure</param>
        /// <returns></returns>
        public SqlDataReader ExecuteStoredProcedure(string spName, SqlParameter[] parameters)
        {
            int spResult;
            return ExecuteStoredProcedure(spName, parameters, out spResult);
        }

        /// <summary>
        /// Executes an stored procedure that does not return rows and closes the connection to the database
        /// </summary>
        /// <param name="spName">The name of the stored procedure that will be excecuted</param>
        /// <param name="parameters">The parameters associated to the stored procedure</param>
        public void ExecuteNoResultStoredProcedure(string spName, SqlParameter[] parameters)
        {
            int spResult;
            ExecuteNoResultStoredProcedure(spName, parameters, out spResult);
        }

        /// <summary>
        /// Executes a query that returns rows, when the CloseReader method is called the connection to the database will be closed.
        /// </summary>
        /// <param name="query">Query that will be excecuted</param>
        /// <param name="parameters">The parameters associated to the query</param>
        /// <returns></returns>
        public SqlDataReader ExecuteQuery(string query, SqlParameter[] parameters)
        {
            return ExecuteQuery(query, parameters, true);
        }

        /// <summary>
        /// Executes an query that does not return rows and closes the connection to the database
        /// </summary>
        /// <param name="query">The name of the stored procedure that will be excecuted</param>
        /// <param name="parameters">The parameters associated to the query</param>
        public void ExecuteNoResultQuery(string query, SqlParameter[] parameters)
        {
            ExecuteNoResultQuery(query, parameters, true);
        }

        #endregion

        #region IDisposable Members

        public void Dispose()
        {
            CloseConnection();
        }

        #endregion

        /// <summary>
        /// Executes an stored procedure that returns rows, when the CloseReader method is called the connection to the database will be closed.
        /// </summary>
        /// <param name="spName">The name of the stored procedure that will be excecuted</param>
        /// <param name="parameters">The parameters associated to the stored procedure</param>
        /// <param name="spResult">Stores the stored procedure result</param>
        /// <returns></returns>
        public SqlDataReader ExecuteStoredProcedure(string spName, SqlParameter[] parameters, out int spResult)
        {
            return ExecuteStoredProcedure(spName, parameters, true, out spResult);
        }

        /// <summary>
        /// Executes an stored procedure that returns rows
        /// </summary>
        /// <param name="spName">The name of the stored procedure that will be excecuted</param>
        /// <param name="parameters">The parameters associated to the stored procedure</param>
        /// <param name="closeConnection">Indicates whether the connection to the database will be closed when the CloseReader method is invoked</param>
        /// <returns></returns>
        public SqlDataReader ExecuteStoredProcedure(string spName, SqlParameter[] parameters, bool closeConnection)
        {
            int spResult;
            return ExecuteStoredProcedure(spName, parameters, true, out spResult);
        }

        /// <summary>
        /// Executes an stored procedure that returns rows
        /// </summary>
        /// <param name="spName">The name of the stored procedure that will be excecuted</param>
        /// <param name="parameters">The parameters associated to the stored procedure</param>
        /// <param name="closeConnection">Indicates whether the connection to the database will be closed when the CloseReader method is invoked</param>
        /// <param name="spResult">Stores the stored procedure result</param>
        /// <returns></returns>
        public SqlDataReader ExecuteStoredProcedure(string spName, SqlParameter[] parameters, bool closeConnection,
                                                    out int spResult)
        {
            SqlCommand command = CreateCommand(spName, parameters, true);
            command.CommandType = CommandType.StoredProcedure;
            SetReader(command, closeConnection);
            spResult = Convert.ToInt32(command.Parameters[0].Value);
            return Reader;
        }

        public void ExecuteNoResultStoredProcedure(string spName, SqlParameter[] parameters, out int spResult)
        {
            ExecuteNoResultStoredProcedure(spName, parameters, true, out spResult);
        }

        public void ExecuteNoResultStoredProcedure(string spName, SqlParameter[] parameters, bool closeConnection)
        {
            int spResult;
            ExecuteNoResultStoredProcedure(spName, parameters, closeConnection, out spResult);
        }

        /// <summary>
        /// Executes an stored procedure that does not return rows
        /// </summary>
        /// <param name="spName">The name of the stored procedure that will be excecuted</param>
        /// <param name="parameters">The parameters associated to the stored procedure</param>
        /// <param name="closeConnection">Indicates whether the connection to the database will be closed</param>
        /// <param name="spResult"></param>
        public void ExecuteNoResultStoredProcedure(string spName, SqlParameter[] parameters, bool closeConnection,
                                                   out int spResult)
        {
            SqlCommand command = CreateCommand(spName, parameters, true);
            command.CommandType = CommandType.StoredProcedure;
            ExecuteNonResult(command, closeConnection);
            spResult = Convert.ToInt32(command.Parameters[0].Value);
        }

        /// <summary>
        /// Executes a query that returns rows
        /// </summary>
        /// <param name="query">The name of the stored procedure that will be excecuted</param>
        /// <param name="parameters">The parameters associated to the query</param>
        /// <param name="closeConnection">Indicates whether the connection to the database will be closed when the CloseReader method is invoked</param>
        public SqlDataReader ExecuteQuery(string query, SqlParameter[] parameters, bool closeConnection)
        {
            SqlCommand command = CreateCommand(query, parameters);
            SetReader(command, closeConnection);
            return Reader;
        }

        /// <summary>
        /// Executes an query that does not return rows
        /// </summary>
        /// <param name="query">The name of the stored procedure that will be excecuted</param>
        /// <param name="parameters">The parameters associated to the query</param>
        /// <param name="closeConnection">Indicates whether the connection to the database will be closed</param>
        public void ExecuteNoResultQuery(string query, SqlParameter[] parameters, bool closeConnection)
        {
            SqlCommand command = CreateCommand(query, parameters);
            ExecuteNonResult(command, closeConnection);
        }

        public void CloseReader()
        {
            if (Reader != null && !Reader.IsClosed)
                Reader.Close();
        }

        private void SetReader(SqlCommand command, bool closeConnection)
        {
            OpenConnection();
            Reader = closeConnection ? command.ExecuteReader(CommandBehavior.CloseConnection) : command.ExecuteReader();
        }

        private void ExecuteNonResult(IDbCommand command, bool closeConnection)
        {
            OpenConnection();
            command.ExecuteNonQuery();
            if (closeConnection)
                CloseConnection();
        }

        private SqlCommand CreateCommand(string text, IEnumerable<SqlParameter> parameters, bool addOutputParameter = false)
        {
            var result = new SqlCommand(text, Connection);
            if (addOutputParameter)
            {
                var outParam = new SqlParameter(OutputParameterName, SqlDbType.Int)
                                   {Direction = ParameterDirection.ReturnValue};
                result.Parameters.Add(outParam);
            }
            foreach (SqlParameter sqlParameter in
                parameters.Where(sqlParameter => sqlParameter.Direction != ParameterDirection.ReturnValue || !addOutputParameter))
            {
                result.Parameters.Add(sqlParameter);
            }
            return result;
        }
    }
}